/**
 * 北京铁塔综合业务管理平台
 * Copyright (c) 2016, 天津智博通信工程有限公司
 * All rights reserved.
 * 
 * 系统版本V1.0
 */

package cn.duhengkui.national.poi.saxexcel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.io.FileUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.input.SAXBuilder;
import org.jdom2.output.Format;
import org.jdom2.output.XMLOutputter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import cn.duhengkui.national.poi.exception.ExcelExportException;
import cn.duhengkui.national.poi.saxexcel.model.ExpExcelModel;
import cn.duhengkui.national.poi.utils.XMLEncoder;



/**   
 * <table border="1">
 *	<tr><td>包名称：</td><td>cn.duhengkui.national.poi.saxexcel</td></tr>
 *	<tr><td>类名称：</td><td>ExpExcel2007Writer</td></tr>
 *	<tr><td>类描述：</td><td>模板读取导出处理方法</td></tr>
 *	<tr><td>创建人：</td><td>杜恒奎</td></tr>
 *  <tr><td>创建时间：</td><td>2018年7月11日/下午3:11:02</td></tr> 
 *  <tr><td>版本：</td><td>1.0.0</td></tr>
 * </table>   
 *     
 */
public class ExpExcel2007Writer{
		
	 
	public static void processModelExcel(OutputStream out,String tmplatePathAndName,JdbcTemplate jdbcTemplate,List<ExpExcelModel> expList,String tmpPath){
		try {
			File tmplateFile = new File(tmplatePathAndName);
			String exTmpFileName = tmpPath + File.separatorChar + UUID.randomUUID().toString() + ".xlsx";
			File exTmpFile = new File(exTmpFileName);
			FileUtils.copyFile(tmplateFile, exTmpFile);
			OPCPackage pkg = POIXMLDocument.openPackage(tmplatePathAndName);
			List<String> sheetNameList = new ArrayList<String>();
			XSSFWorkbook wb = new XSSFWorkbook(exTmpFileName);
			for(int i = 0; i < wb.getNumberOfSheets();i++){
				sheetNameList.add( wb.getSheetAt(i).getPackagePart().getPartName().getName());
			}
			wb.close();
			
			XSSFReader xssfReader = new XSSFReader(pkg);
			Iterator<InputStream> sheets = xssfReader.getSheetsData();

			 Format format = Format.getCompactFormat();  
		     format.setEncoding("utf-8");  
		     format.setIndent(" ");  
			 XMLOutputter XMLOut = new XMLOutputter(format);
			 Map<String, File> tmpFileMap = new HashMap<String, File>();
		     SAXBuilder builder = new SAXBuilder();  
		     Document docFrom;
		    
		     int sheetIndex = 0;
			 while (sheets.hasNext())
		     {
				 	
				 	docFrom = builder.build(sheets.next());
			        if(expList.size() > sheetIndex){
			        	String sheetRef = sheetNameList.get(sheetIndex);
			        	final Set<String> columnNames = new LinkedHashSet<String>(0);
			        	String tmpSheetXmlPath = tmpPath + File.separatorChar + UUID.randomUUID().toString()  + ".xml";
					 	File tmpFile = new File(tmpSheetXmlPath);
					 	boolean isHead = true;
					 	final BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tmpFile),"UTF-8"));
						final SpreadsheetWriter sw = new SpreadsheetWriter(writer);
				        sw.genXmlString();
				        sw.startWorkSheet();
				        Element root = docFrom.getRootElement(); 
				        List<Element> infos = root.getChildren();
			        	 final ExpExcelModel moModel = expList.get(sheetIndex);
			        	 StringBuilder endBf = new StringBuilder();
			        	 
					     for(int i = 0; i < infos.size();i++){
					        	if(!"sheetData".equals(infos.get(i).getName())){
					        		if(isHead){
					        			sw.write(XMLOut.outputString(infos.get(i)).replaceAll("\r|\n", ""));
					        		}else{
					        			endBf.append(XMLOut.outputString(infos.get(i)).replaceAll("\r|\n", ""));
					        		}
					        		
					        	}else{
					        		sw.startSheetData();
					        		List<Element> headRows = infos.get(i).getChildren();
					        		for(int j = 0; j < moModel.getStartIndex();j++){
					        			sw.write(XMLOut.outputString(headRows.get(j)).replaceAll("\r|\n", ""));
					        		}
					        		isHead = false;
					        	}
					      }
					 	jdbcTemplate.query(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
								String sqlStatement = moModel.getSqlAndParams().getSql();
								PreparedStatement statement = con.prepareStatement(sqlStatement,ResultSet.TYPE_SCROLL_INSENSITIVE
										,ResultSet.CONCUR_READ_ONLY);
								for(int i = 0 ; i < moModel.getSqlAndParams().getParams().size() ; i++) {
									statement.setObject(i + 1, moModel.getSqlAndParams().getParams().get(i));
								}
								return statement;
							}
						}, new RowMapper<Object>() {
							boolean flag = true;
							public Object mapRow(ResultSet rs, int rowIndex) throws SQLException {
								try{
						
								if (flag) {
									flag = false;
									ResultSetMetaData rsmd = rs.getMetaData();
									int count = rsmd.getColumnCount();
									for (int i = 0; i < count; i++) {
										columnNames.add(rsmd.getColumnName(i + 1));
									}
								}
								sw.insertRow(rowIndex + moModel.getStartIndex());
								
								if(moModel.getMapper() != null){
									for(Integer col: moModel.getMapper().keySet()){
										String mapName = moModel.getMapper().get(col);
										sw.createCell(col, rs.getObject(mapName) == null? null:XMLEncoder.unencode(rs.getObject(mapName).toString()));
									}
								}else{
									int index = 0;
									for(String colName : columnNames){
										sw.createCell(index, rs.getObject(colName) == null? null:XMLEncoder.unencode(rs.getObject(colName).toString()));
										index++;
									}
								}
	
								
								sw.endRow();
								
								}catch(Exception e){
									try {
										writer.close();
									} catch (IOException e1) {}
									throw new SQLException(e);
								}
								return rowIndex;
							}
						});
					 	  sw.endSheetData();
					 	  sw.write(endBf.toString().replaceAll("\r|\n", ""));
					 	  sw.endWorksheet();
					 	 tmpFileMap.put(sheetRef.substring(1), tmpFile);
					      writer.flush();
					      writer.close();
			        }
			        sheetIndex++;
		     }

			 Excel2007Writer.substitute(exTmpFile, tmpFileMap, out);
				out.close();
				if (exTmpFile.isFile()){
					exTmpFile.delete();
				}
				for(String entry : tmpFileMap.keySet()){
					tmpFileMap.get(entry).delete();
				}
			
			} catch (Exception e) {
				throw new ExcelExportException(e);
			}
	
			
	}
	
/*	public static void main(String[] args){
		ExpExcel2007Writer util = new ExpExcel2007Writer();
		List<ExpExcelModel> moList = new ArrayList<ExpExcelModel>();
		ExpExcelModel mo = new ExpExcelModel();
		mo.setStartIndex(3);
		moList.add(mo);
		util.processModelExcel(null, "E://tmp/测试.xlsx", null, moList, "E://tmp");
	}
*/

	
}
